const mysql  = require('mysql');
const config = require('../config/config.js')
const pool  = mysql.createPool({
    host     : config.mysql.host,
    port     : config.mysql.port,
    user     : config.mysql.username,
    password : config.mysql.password,
    database : config.mysql.dbname
});

exports.query = (sql, values) => {
    return new Promise((resolve, reject) => {
        pool.getConnection((err, connection) => {
            if(err)     return reject('Mysql is closed!')
            connection.query(sql, values, (err, res) => {
                if(err) return reject('Mysql query failed!')
                resolve(res)
                connection.release()
            })
        })
    })
}

/*let query = function( sql, values ) {
    pool.getConnection(function(err, connection) {
        // 使用连接
        connection.query( sql,values, function(err, res) {
            // 使用连接执行查询
            console.log(res)
            connection.release();
            //连接不再使用，返回到连接池
        })
    })
}*/

//获取表名
exports.table = (table) => {
    return `${config.mysql.prefix}${table}`
}
//查找多条记录
exports.findAll = (sql) => {
    return this.query(sql)
}
//查询一列数据
exports.findColumn = async (sql, field='') => {
    let rs = {}
    let res  = await this.query(sql)
    if(res.length>0){
        for(let k in res) {
            rs[k] = res[k][field]
        }
    }
    return rs
}
//查找单条记录
exports.findOne = async (sql, limit=true) => {
    var sql = (limit == true) ? sql+' LIMIT 1 ' : sql
    let rs  = await this.query(sql)
    return (typeof rs[0] === 'undefined') ? {} : rs[0];
}
//查询一个字段
exports.findField = async (sql, field, limit=true) => {
    var sql = (limit == true) ? sql+' LIMIT 1 ' : sql
    let rs  = await this.query(sql)
    return (typeof rs[0] === 'undefined') ? '' : rs[0][field];
}

//插入数据
exports.insert = async (table, params={}) => {
    let sql = ` INSERT INTO `+this.table(table)+ ` SET ? `
    return this.query(sql, params)
}
//更新数据
exports.update = async (table, params={}, condition) => {
    let k='',fields='';
    for(let k in params) {
        fields += `${k}='${params[k]}',`
    }
    fields = fields.replace(/(\,*$)/g, "");
    let sql = `UPDATE `+this.table(table)+ ` SET ${fields} WHERE ${condition}`
    return this.query(sql)
}
//删除数据
exports.delete = async (table, condition='0') => {
    let sql = `DELETE FROM `+this.table(table)+` WHERE ${condition}`
    return this.query(sql)
}
